﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Expressions;
using System.Text;
using PUBCOM;

namespace YTBZ.BUSINESSLOGIC
{
    public class XDBLL : ICBST
    {
        ////获取纸箱包装信息
        //public DataTable GetZXBZXX(SortedList SDList, out int RecordCount)
        //{
        //    string sqlReturnFiled;
        //    string sqlTableName;
        //    string sqlWhere;

        //    #region  sql文
        //    #region  查询字段
        //    sqlReturnFiled = @"*,XDBZID KEYID";
        //    #endregion

        //    #region  查询表名
        //    sqlTableName = @"YT_S_XDBZXX";
        //    #endregion

        //    #region  Where条件
        //    sqlWhere = @"WHERE ('{0}' = '' OR OrderBH LIKE '%{0}%')
        //                   AND ('{1}' = '' OR PCNum = '{1}')
        //                   AND ('{2}' = '' OR INTIME >= CONVERT(DATETIME,'{2}'))
        //                   AND ('{3}' = '' OR INTIME <= CONVERT(DATETIME,'{3}'))";
        //    sqlWhere = String.Format(sqlWhere, SDList["V_OrderBH"], SDList["V_PCNum"], SDList["V_InTimeB"], SDList["V_InTimeE"]);
        //    #endregion
        //    #endregion

        //    QueryParam qp = new QueryParam();
        //    qp.ReturnFiled = sqlReturnFiled;
        //    qp.TableName = sqlTableName;
        //    qp.Where = sqlWhere;
        //    qp.PageSize = DataConverter.CLng(SDList["V_CURRPAGESIZE"]);
        //    qp.PageIndex = DataConverter.CLng(SDList["V_CURRPAGE"]);
        //    qp.OrderType = 1;
        //    qp.OrderField = "InTime";
        //    return GetList(qp, out RecordCount);
        //}

        //获取纸箱包装信息(分页)
        public DataTable GetXDBZXX(SortedList SDList, out int RecordCount)
        {
            #region  sql文
            #region 停用
            //string sql = @"SELECT *,XDBZID KEYID FROM YT_S_XDBZXX
            //             WHERE ({0} = '' OR OrderBH LIKE '%{0}%')
            //               AND ({1} = '' OR PCNum = {1})
            //               AND ({2} = '' OR INTIME >= CONVERT(DATETIME,{2}))
            //               AND ({3} = '' OR INTIME <= CONVERT(DATETIME,{3})+1)
            //               ORDER BY INTIME DESC";
            //sql = String.Format(sql, Input.AddDYH(SDList["@OrderBH"]), Input.AddDYH(SDList["@PCNum"]),
            //                    Input.AddDYH(SDList["@InTimeB"]), Input.AddDYH(SDList["@InTimeE"]));
            //sql= Input.ReplaceSQLParm(sql);
            #endregion

            string sql = @"SELECT *,XDBZID KEYID FROM YT_S_XDBZXX
                         WHERE (@OrderBH = '' OR OrderBH LIKE '%@OrderBH%')
                           AND (@PCNum = '' OR PCNum = @PCNum)
                           AND (@InTimeB = '' OR INTIME >= CONVERT(DATETIME,@InTimeB))
                           AND (@InTimeE = '' OR INTIME <= CONVERT(DATETIME,@InTimeE)+1)
                           ORDER BY INTIME DESC,TXBH DESC,ZXXH DESC";
            #endregion
            return GetPageList(sql, SDList, out RecordCount);
        }
        //获取箱单包装信息
        public DataTable GetXDBZXX(SortedList SDList)
        {
            string sql = @"SELECT * FROM YT_S_XDBZXX
                         WHERE (@OrderBH = '' OR OrderBH = @OrderBH)
                           AND (@PCNum = '' OR PCNum = @PCNum)
                           AND (@TXBH = '' OR TXBH = @TXBH)
                           AND (@ZXXH = '' OR ZXXH = @ZXXH)
                        ORDER BY ZXXH";
            return DbHelperSQL.Query(sql, SDList);
        }
        //获取纸箱信息
        public DataTable GetZX(SortedList SDList)
        {
            string sql = @"SELECT OrderBH,PCNum,ZXBH,ZXCC,ZXXH FROM YT_S_XDBZXX
                         WHERE OrderBH = @OrderBH
                           AND PCNum = @PCNum
                           AND ZXCC <> ''
                       GROUP BY OrderBH,PCNum,ZXBH,ZXCC,ZXXH
                       ORDER BY ZXXH";
            return DbHelperSQL.Query(sql, SDList);
        }

        //获取铁箱信息
        public DataTable GetTX(SortedList SDList)
        {
            string sql = @"SELECT OrderBH,PCNum,TXBH FROM YT_S_XDBZXX
                         WHERE OrderBH = @OrderBH
                           AND PCNum = @PCNum
                       GROUP BY OrderBH,PCNum,TXBH
                       ORDER BY TXBH";
            return DbHelperSQL.Query(sql, SDList);
        }


        //停用
        //public int AddZXBZXX(SortedList SDList)
        //{
        //    string sql = @"INSERT INTO YT_S_XDBZXX(OrderBH, PCNum, CX, ZXBH, ZXCC,WLBH, WLZWMC, WLEWMC, WLZXSL)
        //                   VALUES(@OrderBH, @PCNum, @CX, @ZXBH, @ZXCC, @WLBH, @WLZWMC, @WLEWMC, @WLZXSL)";
        //    SqlParameter[] parStock = new SqlParameter[9];
        //    parStock[0] = new SqlParameter("@OrderBH", SDList["V_OrderBH"]);
        //    parStock[1] = new SqlParameter("@PCNum", SDList["V_PCNum"]);
        //    parStock[2] = new SqlParameter("@CX", SDList["V_CX"]);
        //    parStock[3] = new SqlParameter("@ZXBH", SDList["V_ZXBH"]);
        //    parStock[4] = new SqlParameter("@ZXCC", SDList["V_ZXCC"]);
        //    parStock[5] = new SqlParameter("@WLBH", SDList["V_WLBH"]);
        //    parStock[6] = new SqlParameter("@WLZWMC", SDList["V_WLZWMC"]);
        //    parStock[7] = new SqlParameter("@WLEWMC", SDList["V_WLEWMC"]);
        //    parStock[8] = new SqlParameter("@WLZXSL", SDList["V_WLZXSL"]);
        //    return DbHelperSQL.ExecuteSql(sql, parStock);
        //}

        public int AddXDBZXX(SortedList SDList)
        {
            string sql = @"INSERT INTO YT_S_XDBZXX(OrderBH, PCNum, CX, PCS, TXBH, ZXBH, ZXCC,WLBH, WLZWMC, WLEWMC, WLZXSL,TXCC,TXJZ,TXMZ,ZXXH)
                           VALUES(@OrderBH, @PCNum, @CX, @PCS, @TXBH, @ZXBH, @ZXCC, @WLBH, @WLZWMC, @WLEWMC, @WLZXSL,@TXCC,@TXJZ,@TXMZ,@ZXXH)";
            return DbHelperSQL.ExecuteSql(sql, SDList);
        }

        public int DelZXBZXX(SortedList SDList)
        {
            string sql = "delete from YT_S_XDBZXX where OrderBH=@OrderBH and PCNum=@PCNum";
            return DbHelperSQL.ExecuteSql(sql, SDList);
        }
    }
}